<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Getting database metadata</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Visual Basic, SQLite, ADO.NET, database metadata, learn SQLite Visual Basic ">
<meta name="description" content="In this part of the SQLite Visual Basic tutorial, we work with database metadata.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">

<div>
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

</div>


<div class="content2">

<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Getting database metadata</h1>

<p>
Metadata is information about the data in the database. 
Metadata in SQLite contains information about the tables 
and columns, in which we store data. Number of rows affected 
by an SQL statement is metadata. Number of rows and columns returned 
in a result set belong to metadata as well.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
Metadata in SQLite can be obtained using the PRAGMA command. 
SQLite objects may have attributes, which are metadata. Finally, 
we can also obtain specific metatada from querying the SQLite 
system sqlite_master table.
</p>


<pre class="code">
Option Strict On

Imports Mono.Data.Sqlite

Module Example

    Sub Main()

        Dim cs As String = "URI=file:test.db"
        Dim nrows As String

        Try 
            Console.Write("Enter rows to fetch: ")
            nrows = Console.ReadLine()
        Catch e As FormatException
            Console.WriteLine(e.ToString())
        End Try

        Using con As New SqliteConnection(cs)
        
            con.Open()
        
            Using cmd As New SqliteCommand(con)
            
                cmd.CommandText = "SELECT * FROM Cars LIMIT @Id"
                cmd.Prepare()

                cmd.Parameters.AddWithValue("@Id", Int32.Parse(nrows))

                Dim cols As Integer = 0
                Dim rows As Integer = 0

                Dim rdr As SqliteDataReader = cmd.ExecuteReader()

                Using rdr

                    cols = rdr.FieldCount
                    rows = 0

                    While rdr.Read()                    
                        rows += 1
                    End While

                    Console.WriteLine("The query fetched {0} rows", rows)
                    Console.WriteLine("Each row has {0} cols", cols)
                End Using    
            End Using

            con.Close()
        End Using
        
    End Sub

End Module
</pre>

<p>
In the above example, we get the number of rows and columns returned
by a query. 
</p>

<pre class="explanation">
Try 
    Console.Write("Enter rows to fetch: ")
    nrows = Console.ReadLine()
Catch e As FormatException
    Console.WriteLine(e.ToString())
End Try
</pre>

<p>
The example asks for the number of rows on the command line.
</p>

<pre class="explanation">
cmd.CommandText = "SELECT * FROM Cars LIMIT @Id"
cmd.Prepare()

cmd.Parameters.AddWithValue("@Id", Int32.Parse(nrows))
</pre>

<p>
We select as many rows as we have provided on the command line.
</p>

<pre class="explanation">
cols = rdr.FieldCount
</pre>

<p>
The number of returned columns can be retrieved from the 
<code>FieldCount</code> property of the <code>SqliteDataReader</code>
object. 
</p>

<pre class="explanation">
While rdr.Read()                    
    rows += 1
End While
</pre>

<p>
We count the number of rows in the result set. 
</p>

<pre>
$ mono fields_rows.exe 
Enter rows to fetch: 5
The query fetched 5 rows
Each row has 3 cols
</pre>

<p>
Output.
</p>


<h2>Column headers</h2>

<p>
Next we will show, how to print column headers with the data
from a database table.
</p>

<pre class="code">
Option Strict On

Imports Mono.Data.Sqlite

Module Example

    Sub Main()

        Dim cs As String = "URI=file:test.db"

        Using con As New SqliteConnection(cs)
        
            con.Open()
        
            Using cmd As New SqliteCommand(con)

                cmd.CommandText = "SELECT * FROM Cars LIMIT 5"
                
                Dim rdr As SqliteDataReader = cmd.ExecuteReader()

                Using rdr
                
                    Console.WriteLine(String.Format("{0, -3} {1, -8} {2, 8}", _
                        rdr.GetName(0), rdr.GetName(1), rdr.GetName(2)))

                    While rdr.Read()                    
                        Console.WriteLine(String.Format("{0, -3} {1, -8} {2, 8}", _
                            rdr.GetInt32(0), rdr.GetString(1), rdr.GetInt32(2)))
                    End While
         
                End Using        
            End Using

            con.Close()
        End Using
       
    End Sub

End Module
</pre>

<p>
In this program, we select 5 rows from the Cars table
with their column names. 
</p>

<pre class="explanation">
Dim rdr As SqliteDataReader = cmd.ExecuteReader()
</pre>

<p>
We create a <code>SqliteDataReader</code> object. 
</p>


<pre class="explanation">
Console.WriteLine(String.Format("{0, -3} {1, -8} {2, 8}", _
    rdr.GetName(0), rdr.GetName(1), rdr.GetName(2)))
</pre>

<p>
We get the names of the columns with the <code>GetName()</code> 
method of the reader. The <code>String.Format()</code> method is
used to format the data. 
</p>

<pre class="explanation">
While rdr.Read()                    
    Console.WriteLine(String.Format("{0, -3} {1, -8} {2, 8}", _
        rdr.GetInt32(0), rdr.GetString(1), rdr.GetInt32(2)))
End While
</pre>

<p>
We print the data that was returned by the SQL statement
to the terminal. 
</p>

<pre>
$ mono headers.exe 
Id  Name        Price
1   Audi        52642
2   Mercedes    57127
3   Skoda        9000
4   Volvo       29000
5   Bentley    350000
</pre>

<p>
Ouput of the program. 
</p>


<h2>Affected rows</h2>

<p>
In the following example, we will find out how many changes have
been done by a particular SQL command.
</p>

<pre class="code">
Option Strict On

Imports Mono.Data.Sqlite

Module Example

    Sub Main()

        Dim cs As String = "Data Source=:memory:"

        Using con As New SqliteConnection(cs)
        
            con.Open()
        
            Using cmd As New SqliteCommand(con)

                cmd.CommandText = "CREATE TABLE Friends(Id INT, Name TEXT)"
                cmd.ExecuteNonQuery()
                cmd.CommandText = "INSERT INTO Friends VALUES(1, 'Tom')"
                cmd.ExecuteNonQuery()
                cmd.CommandText = "INSERT INTO Friends VALUES(2, 'Jane')"
                cmd.ExecuteNonQuery()
                cmd.CommandText = "INSERT INTO Friends VALUES(3, 'Rebekka')"
                cmd.ExecuteNonQuery()
                cmd.CommandText = "INSERT INTO Friends VALUES(4, 'Lucy')"
                cmd.ExecuteNonQuery()
                cmd.CommandText = "INSERT INTO Friends VALUES(5, 'Robert')"
                cmd.ExecuteNonQuery()

                cmd.CommandText = "DELETE FROM Friends WHERE Id IN (3, 4, 5)"
                Dim n As Integer = cmd.ExecuteNonQuery()

                Console.WriteLine("The statement has affected {0} rows", n)
        
            End Using

            con.Close()

        End Using
        
    End Sub

End Module
</pre>

<p>
We create a Friends table in memory. In the last SQL command, we delete three
rows. The <code>ExecuteNonQuery()</code> method returns the number of 
rows affected by the last SQL command.
</p>

<pre class="explanation">
cmd.CommandText = "DELETE FROM Friends WHERE Id IN (3, 4, 5)"
</pre>

<p>
In this SQL statement, we delete three rows. 
</p>

<pre class="explanation">
Dim n As Integer = cmd.ExecuteNonQuery()
</pre>

<p>
We find out the number of changes done by the last SQL
statement. 
</p>

<pre>
$ mono affected_rows.exe 
The statement has affected 3 rows
</pre>

<p>
Example output.
</p>


<h2>Table schema</h2>

<p>
There is a <code>GetSchemaTable()</code> method which returns metadata 
about each column. It returns many values, among others the column
name, column size, the base table name or whether the column is unique
or not. 
</p>

<pre class="code">
Option Strict On

Imports System.Data
Imports Mono.Data.Sqlite

Module Example

    Sub Main()

        Dim cs As String = "URI=file:test.db"

        Using con As New SqliteConnection(cs)
        
            con.Open()
        
            Using cmd As New SqliteCommand(con)

                cmd.CommandText = "SELECT * FROM Cars LIMIT 4"
                
                Dim rdr As SqliteDataReader = cmd.ExecuteReader()

                Using rdr
                
                    Dim schemaTable As DataTable = rdr.GetSchemaTable()

                    For Each row As DataRow In schemaTable.Rows                    
                        For Each col As DataColumn In schemaTable.Columns
                            Console.WriteLine(col.ColumnName &amp; " = " &amp; row(col))
                        Next
                        Console.WriteLine()
                    Next
         
                End Using
            End Using

            con.Close()
        End Using
        
    End Sub

End Module
</pre>

<p>
The example prints lots of metadata about table columns. 
</p>

<pre class="explanation">
Dim schemaTable As DataTable = rdr.GetSchemaTable()
</pre>

<p>
We get the database schema table. 
</p>

<pre class="explanation">
For Each row As DataRow In schemaTable.Rows                    
    For Each col As DataColumn In schemaTable.Columns
        Console.WriteLine(col.ColumnName &amp; " = " &amp; row(col))
    Next
    Console.WriteLine()
Next
</pre>

<p>
We go through the schema table rows, which hold the metadata, and
print them to the console. 
</p>


<pre>
$ mono table_schema.exe 
ColumnName = Id
ColumnOrdinal = 0
ColumnSize = 8
NumericPrecision = 19
NumericScale = 0
IsUnique = True
IsKey = True
...
</pre>

<p>
Excerpt from the example output.
</p>


<h2>Table names</h2>

<p>
In our last example related to the metadata, we will 
list all tables in the test.db database. 
</p>

<pre class="code">
Option Strict On

Imports Mono.Data.Sqlite

Module Example

    Sub Main()

        Dim cs As String = "URI=file:test.db"

        Using con As New SqliteConnection(cs)
        
            con.Open()
        
            Using cmd As New SqliteCommand(con)

                cmd.CommandText = "SELECT name FROM sqlite_master " _
                    &amp; "WHERE type='table' ORDER BY name"
                
                Dim rdr As SqliteDataReader = cmd.ExecuteReader()

                Using rdr                
                    While (rdr.Read())
                        Console.WriteLine(rdr.GetString(0))
                    End While         
                End Using        
            End Using

            con.Close()
        End Using
        
    End Sub

End Module
</pre>

<p>
The code example prints all available tables in the chosen database
to the terminal. 
</p>

<pre class="explanation">
cmd.CommandText = "SELECT name FROM sqlite_master " _
    &amp; "WHERE type='table' ORDER BY name"
</pre>

<p>
The table names are retrieved from the <code>sqlite_master</code> table.
</p>

<pre>
$ mono table_names.exe
Cars
Friends2
Images
</pre>

<p>
These were the tables on our system. 
</p>

<p>
In this part of the SQLite Visual Basic tutorial, we have worked with database
metadata. 
</p>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 29, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

